﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class DepartmentManager:ConnectionManager
    {
        public DataTable GetDepartment()
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_DEPT";
            DataSet ds = new DataSet();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);


            return ds.Tables[0];

        }

        public bool IsDepartmentExists(string sDescription)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_DEPT WHERE DeptName = @DeptName";
            oCommand.Parameters.Add(new SqlParameter("@DeptName", sDescription));

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            int iCount = ds.Tables[0].Rows.Count;

            if (iCount > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public SqlException AddDepartment(string sDescription)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO M_DEPT (DeptName, DocStatus) VALUES (@DeptName, 'Active')";
                oCommand.Parameters.Add(new SqlParameter("@DeptName", sDescription));

                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }

        public SqlException UpdateDepartment(DataTable dt)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                foreach (DataRow oRow in dt.Rows)
                {
                    if (oRow.RowState == DataRowState.Modified)
                    {
                        oCommand = new SqlCommand();

                        oCommand.Connection = this.Connection;
                        oCommand.CommandText = "UPDATE M_DEPT set DeptName=@DeptName, DocStatus=@DocStatus WHERE RecNo=@RecNo";
                        oCommand.Parameters.Add(new SqlParameter("@RecNo", oRow["RecNo"]));
                        oCommand.Parameters.Add(new SqlParameter("@DeptName", oRow["DeptName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@DocStatus", oRow["DocStatus"].ToString()));

                        oCommand.ExecuteNonQuery();
                    }
                }

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }
    }
}
